package com.etc.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

public class DBUtil {
	private static String url = "jdbc:mysql://localhost:3306/fras?useUnicode=true&characterEncoding=UTF-8";
	private static String uname = "root";
	private static String pwd = "1234";

	//获取连接
	private static Connection getConn() throws SQLException {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return DriverManager.getConnection(url, uname, pwd);
	}
	//修改
	public static boolean executeUpdate(String sql,Object...starams) {
		Connection conn = null;
		PreparedStatement ps = null;
		int n = 0;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			addStarms(ps, starams);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeAll(conn, ps, null);
		}
		return n > 0;
	}
	//查询
	public static CachedRowSet executeQuery(String sql,Object...starams) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		CachedRowSet crs = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			addStarms(ps, starams);
			rs = ps.executeQuery();
			crs = new CachedRowSetImpl();
			crs.populate(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			closeAll(conn, ps, rs);
		}
		return crs;
	}

	private static void addStarms(PreparedStatement pStatement,Object...starams) {
		if(starams.length==0)
			return;
		for (int i = 0; i < starams.length; i++) {
			try {
				pStatement.setObject(i+1, starams[i]);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private static void closeAll(Connection conn,PreparedStatement pStatement,ResultSet rs) {
		try {
			if(rs!=null)
				rs.close();
			if(pStatement!=null)
				pStatement.close();
			if(conn!=null)
				conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void closeCrs(CachedRowSet crs) {
		try {
			if(crs!=null)
				crs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
